package com.qingcheng.dao;

import com.qingcheng.pojo.order.TradeReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface TradeReportMapper extends Mapper<TradeReport> {

    /**
     * 查询: 下单人数,订单数 下单件数 下单金额
     * @param date
     * @return
     */
    @Select("SELECT COUNT(DISTINCT o.username) orderPeopleNum, COUNT(o.id) orderNum, SUM(oi.num) orderTotalNum,SUM(o.pay_money) orderMoney " +
            "from tb_order As o,tb_order_item AS oi " +
            "where o.id=oi.order_id AND DATE_FORMAT(o.create_time,'%Y-%m-%d')=#{date}")
    public TradeReport findOrderNumAndOrderTotalNumAndOrderMoney(@Param("date") LocalDate date);

    /**
     * 查询: 有效订单数
     * @param date
     * @return
     */
    @Select("SELECT count(*) effectiveOrderNum " +
            "from tb_order " +
            "where DATE_FORMAT(create_time,'%Y-%m-%d')=#{date} AND is_delete='0' AND order_status < 4")
    public int findEffectiveOrderNum(@Param("date") LocalDate date);

    /**
     * 查询: 退款金额
     * @param date
     * @return
     */
    @Select("SELECT sum(oi.pay_money) refundMoney " +
            "from tb_order_item AS oi ,tb_order AS o " +
            "WHERE oi.order_id=o.id AND DATE_FORMAT(o.create_time,'%Y-%m-%d')=#{date} AND oi.is_return='1' AND o.order_status='4'")
    public int findRefundMoney(@Param("date") LocalDate date);

    /**
     * 查询: 付款人数 付款订单数 付款件数 付款金额 客单价
     * @param date
     * @return
     */
    @Select("SELECT COUNT(DISTINCT o.username) payPeopleNum,COUNT(o.id) payOrderNum,SUM(oi.num) payOrderTotalNum," +
            "SUM(o.pay_money) payTotalMoney,ROUND((SUM(o.pay_money)/COUNT(DISTINCT o.username))) paySingleMoney " +
            "from tb_order as o,tb_order_item AS oi " +
            "WHERE DATE_FORMAT(o.create_time,'%Y-%m-%d')=#{date} AND o.pay_status='1'")
    public TradeReport findPayPeopleNumAndPayOrderNumAndPayOrderTotalNumAndPayTotalMoneyAndPaySingleMoney(@Param("date") LocalDate date);

    /**
     * 查询: 统计时间
     * @param date
     * @return
     */
    @Select("SELECT DISTINCT DATE_FORMAT(create_time,'%Y-%m-%d') " +
            "FROM tb_order " +
            "WHERE DATE_FORMAT(create_time,'%Y-%m-%d')=#{date}")
    public Date findCountDate(@Param("date") LocalDate date);

    /**
     * 查询某个时间段交易统计报表
     */
//    @Select("SELECT * " +
//            "FROM tb_trade_report " +
//            "WHERE DATE_FORMAT(count_date,'%Y-%m-%d')>=#{date1} AND DATE_FORMAT(count_date,'%Y-%m-%d')<=#{date2}")
    @Select("SELECT sum(visit_people_num) visitPeopleNum,sum(order_people_num) orderPeopleNum,sum(order_num) orderNum,sum(order_total_num) orderTotalNum,sum(effective_order_num) effectiveOrderNum, " +
            "sum(order_money) orderMoney,sum(refund_money) refundMoney,sum(pay_people_num) payPeopleNum,sum(pay_order_num) payOrderNum,sum(pay_order_total_num)payOrderTotalNum, " +
            "sum(pay_total_money) payTotalMoney,sum(pay_total_money)/sum(pay_people_num) paySingleMoney " +
            "FROM tb_trade_report " +
            "WHERE count_date>=#{date1} AND count_date<=#{date2}")
    public List<Map> tradeReport(@Param("date1") String date1, @Param("date2") String date2);

    /**
     * 查询某个时间段具体交易统计报表
     */
    @Select("SELECT visit_people_num visitPeopleNum,order_people_num orderPeopleNum,order_num orderNum,order_total_num orderTotalNum,effective_order_num effectiveOrderNum, " +
            "order_money orderMoney,refund_money refundMoney,pay_people_num payPeopleNum,pay_order_num payOrderNum,pay_order_total_num payOrderTotalNum, " +
            "pay_total_money payTotalMoney,pay_total_money/pay_people_num paySingleMoney,DATE_FORMAT(count_date,'%Y-%m-%d') countDate " +
            "FROM tb_trade_report " +
            "WHERE count_date>=#{date1} AND count_date<=#{date2} ORDER BY count_date ")
    public List<Map> tradeReport2(@Param("date1") String date1, @Param("date2") String date2);

}
